﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Windows.Forms;
using Db4objects.Db4o;
using NmbpProject2.DboClasses;
using Oracle.DataAccess.Client;

namespace NmbpProject2.DataLayer
{
    internal class CategoryDL
    {
        /// <summary>
        /// zak
        /// radi i za oracle
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public Category findByName(string name)
        {
            switch (DbConfig.DBTYPE)
            {
                case dbtype.Db40:
                    IList<Category> categories =
                        DataConfig.db.Query(delegate(Category match) { return match.Name == name; });
                    if (categories.Count > 0)
                    {
                        Category category = categories[0];
                        return category;
                    }
                    return null;
                case dbtype.Oracle:
                    try
                    {
                        var oracle = new OracleConnectionClass();
                        oracle.openConnection();
                        var cmd = new OracleCommand();
                        cmd.Connection = oracle.conn;
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = string.Format("select from categories where name='{0}'", name);
                        cmd.ExecuteNonQuery();
                        oracle.closeConnection();
                    }
                    catch (Exception e)
                    {
                    }
                    break;
            }
            return null;
        }

        /// <summary>
        /// zak
        /// radi i za oracle
        /// </summary>
        /// <param name="name"></param>
        public void insertCategory(string name)
        {
            switch (DbConfig.DBTYPE)
            {
                case dbtype.Db40:
                    Category category = new Category(name);
                    DataConfig.db.Store(category);
                    break;
                case dbtype.Oracle:
                    try
                    {
                        var oracle = new OracleConnectionClass();
                        oracle.openConnection();
                        var cmd = new OracleCommand();
                        cmd.Connection = oracle.conn;
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = string.Format("insert into categories values ('{0}')", name);
                        cmd.ExecuteNonQuery();
                        oracle.closeConnection();
                    }
                    catch (Exception e)
                    {
                        MessageBox.Show(e.Message);
                    }
                    break;
            }
        }

        /// <summary>
        /// edit:zak
        /// vraca kompletnu listu kategorija
        /// dodana funkcionalnost za oracle
        /// </summary>
        /// <returns></returns>
        public List<Category> getAllCategories()
        {
            switch (DbConfig.DBTYPE)
            {
                case dbtype.Db40:
                    IObjectSet result = DataConfig.db.QueryByExample(typeof(Category));
                    List<Category> categories = new List<Category>();
                    foreach (Category category in result)
                    {
                        categories.Add(category);
                    }
                    result = null;
                    return categories;
                case dbtype.Oracle:
                    try
                    {
                        var oracle = new OracleConnectionClass();
                        oracle.openConnection();
                        var dt = new DataTable();
                        var cmd = new OracleCommand("select name from categories", oracle.conn);
                        cmd.CommandType = CommandType.Text;
                        new OracleDataAdapter(cmd).Fill(dt);
                        var lang = new List<Category>();
                        foreach (DataRow row in dt.Rows)
                        {
                            lang.Add(new Category(row[0].ToString()));
                        }
                        return lang;
                    }
                    catch (Exception e)
                    {
                        return null;
                    }
            }
            return null;
        }

        /// <summary>
        /// zak
        /// radi i za oracle
        /// </summary>
        /// <param name="category"></param>
        public void delete(Category category)
        {
            switch (DbConfig.DBTYPE)
            {
                case dbtype.Db40:
                    DataConfig.db.Delete(category);
                    break;
                case dbtype.Oracle:
                    try
                    {
                        var oracle = new OracleConnectionClass();
                        oracle.openConnection();
                        var cmd =
                            new OracleCommand(
                                string.Format("delete from categories where name = '{0}'",
                                category.Name), oracle.conn) { CommandType = CommandType.Text };
                        cmd.ExecuteNonQuery();
                        cmd.CommandText = "delete from competitionscategories where deref(category).name is null";
                        cmd.ExecuteNonQuery();
                        cmd.CommandText = "delete from applicationscategories where deref(category).name is null";
                        cmd.ExecuteNonQuery();

                        oracle.closeConnection();
                    }
                    catch (Exception e)
                    {
                        MessageBox.Show(e.Message);
                    }
                    break;
            }


        }

        /// <summary>
        /// zak
        /// radi i za oracle
        /// </summary>
        /// <param name="category"></param>
        /// <param name="Name"></param>
        public void update(Category category, string Name)
        {
            switch (DbConfig.DBTYPE)
            {
                case dbtype.Db40:
                    Category updatedCategory = (Category)DataConfig.db.QueryByExample(category)[0];
                    updatedCategory.Name = Name;
                    DataConfig.db.Store(updatedCategory);
                    break;
                case dbtype.Oracle:
                    try
                    {
                        var oracle = new OracleConnectionClass();
                        oracle.openConnection();
                        var cmd = new OracleCommand()
                                      {
                                          CommandType = CommandType.Text,
                                          Connection = oracle.conn,
                                          CommandText =
                                              string.Format("update categories set name='{0}' where name='{1}'", Name,
                                                            category.Name)
                                      };
                        cmd.ExecuteNonQuery();
                        oracle.closeConnection();

                    }
                    catch (Exception e)
                    {
                        MessageBox.Show(e.Message);
                    }
                    break;
            }

        }

        /// <summary>
        /// vraca iz oracla sve kategorije po uvijetu.
        /// edit:zak
        /// </summary>
        /// <param name="table">tablica iz koje dolaze</param>
        /// <param name="condition">uvijet dohvata</param>
        /// <returns>lista kategorija</returns>
        public static List<Category> getAllCategories(string table, string condition)
        {
            try
            {
                var oracle = new OracleConnectionClass();
                oracle.openConnection();
                var dt = new DataTable();
                var cmd = new OracleCommand(
                    string.Format("select deref(category).name from {0} where {1}",
                                  table, condition), oracle.conn);
                cmd.CommandType = CommandType.Text;
                new OracleDataAdapter(cmd).Fill(dt);
                var lang = new List<Category>();
                foreach (DataRow row in dt.Rows)
                {
                    lang.Add(new Category(row[0].ToString()));
                }
                return lang;
            }
            catch (Exception e)
            {

            }
            return null;
        }

    }
}